package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Dictionary;
import cn.edu.sdjzu.xg.kcsj.exception.BysjException;
import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class DictionaryDao {
    private static DictionaryDao dictionaryDao = new DictionaryDao();
    private static Collection<Dictionary> dictionaries;
    private DictionaryDao() {
    }

    public static DictionaryDao getInstance() {
        return dictionaryDao;
    }
    public Collection<Dictionary> findAll(List<Condition> conditionList, Pagination pagination, Connection connection) throws SQLException {
        Collection<Dictionary>dDictionaries = new HashSet<Dictionary>();
        StringBuilder sql_select = new StringBuilder(" select * from dictionary ");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"dictionary",conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement p1 = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = p1.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            Dictionary dictionary = new Dictionary(id,description);
            dDictionaries.add(dictionary);
        }
        JdbcHelper.close(resultSet,p1);
        return dDictionaries;
    }
    public Collection<Dictionary> findA(Connection connection) throws SQLException {
        Collection<Dictionary>dDictionaries = new HashSet<Dictionary>();
        String sql_select = "select * from dictionary";
        PreparedStatement p1 = connection.prepareStatement(sql_select);
        ResultSet resultSet = p1.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            Dictionary dictionary = new Dictionary(id,description);
            dDictionaries.add(dictionary);
        }
        JdbcHelper.close(resultSet,p1);
        return dDictionaries;
    }
    public Dictionary find(Integer id, Connection connection) throws SQLException{
        Dictionary dictionary = null;
        String select_s = "select * from dictionary WHERE id=?";
        PreparedStatement p1 = connection.prepareStatement(select_s);
        p1.setInt(1,id);
        ResultSet resultSet = p1.executeQuery();
        if (resultSet.next()){
            String description = resultSet.getString("description");
            dictionary = new Dictionary(id,description);
        }
        JdbcHelper.close(resultSet,p1);
        return dictionary;
    }
    public boolean delete(Integer id,Connection connection) throws SQLException {
        PreparedStatement p = connection.prepareStatement("DELETE FROM dictionary WHERE id=?");
        p.setInt(1,id);
        if (p.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }
    public boolean update(Dictionary dictionary,Connection connection) throws SQLException{
        String update_s = "UPDATE dictionary SET description=? WHERE id=?";
        PreparedStatement p3 = connection.prepareStatement(update_s);
        p3.setString(1, dictionary.getDescription());
        p3.setInt(2,dictionary.getId());
        if (p3.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }

    public boolean add(Dictionary dictionary,Connection connection) throws SQLException{
        String s_ins = "INSERT INTO dictionary (description) VALUES (?)";
        PreparedStatement p = connection.prepareStatement(s_ins);
        p.setString(1, dictionary.getDescription());
        if (p.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }
}
